package com.hlbb.dao;

import java.util.Date;
import java.util.List;

import org.hibernate.Query;
import org.springframework.stereotype.Repository;

import com.hlbb.domain.bean.Customer;


@Repository("customerDAO")
public class CustomerDAO extends BaseHelperDAO {

	public static final String CUSTOMER_NAME	= "customerName";
	public static final String LOGIN_ID 	 	= "loginId";

	public List<Customer> findByCustomerName(Object customerName) throws Exception {
		return findByProperty(CUSTOMER_NAME, customerName);
	}
	
	public List<Customer> findByLoginId(Object loginId) throws Exception {
		return findByProperty(LOGIN_ID, loginId);
	}
	
	@SuppressWarnings("unchecked")
	public List<Customer> findByProperty(String propertyName, Object value) throws Exception {

		try {

			String queryString = "from Customer as model where model."
					+ propertyName + "= :propertyName";
			Query query = getCurrentSession().createQuery(queryString);
			query.setParameter("propertyName", value);
			return query.list();
		} catch (RuntimeException re) {
			throw re;
		}
	}
	
	@SuppressWarnings("unchecked")
	public List<Customer> listCgsCustomer() throws Exception {
		
		// HQL
		String queryString = "select new Customer(customerId,customerName) from Customer"; // Hibernate Query Language
		List<Customer> result = getCurrentSession().createQuery(queryString).list();

		// SQL: need to list all columns that have been mapped in BEAN 
		/* 
		String queryString = "select CUSTOMER_ID, CUSTOMER_NAME from CUSTOMER "; // Hibernate Query Language
		List<Customer> result = getCurrentSession().createSQLQuery(queryString)
				.addEntity(Customer.class).list();
		*/
		
		// SQL: can set entity with an given alias
		/*
		String queryString = "select ii.*, cc.* from TBL_TEST_CUSTOMER ii, CUSTOMER cc where ii.customer_id = cc.customer_id "; // Hibernate Query Language
		List<Customer> result = getCurrentSession().createSQLQuery(queryString)
				//.addScalar("customer_Id", LongType.INSTANCE)
				//.addScalar("customer_Name", StringType.INSTANCE)
				.addEntity("ii", Customer.class).list();
		*/
		return result;
	}
	
	/**
	 * Insert customer into database
	 */
	public void addCustomer(Customer entity) throws Exception {
		
		// Insert into database
		getCurrentSession().save(entity);
		
	}
	
	public void listTest() throws Exception {
		
		try {
		
		StringBuffer sqlStmt = new StringBuffer("");
		Date startDate = new Date(2013-1900, 1,1);
		Date endDate   = new Date(2014-1900, 1,1);
		
		
		
		long tStartDate = com.checkfree.isolutions.utility.DateUtility
				.asJulian(startDate);

		long tEndDate = com.checkfree.isolutions.utility.DateUtility
				.asJulian(endDate);
		
		sqlStmt.append("select s.instanceid from statement s, account a ");

		sqlStmt.append("where s.accountid = a.id ");

		sqlStmt.append("and a.externalaccountkey = :externalaccountkey ");

		sqlStmt.append("and s.state = :state ");

		sqlStmt.append("and s.statementdate between :statementdate1 and :statementdate2 ");

		sqlStmt.append("order by statementdate desc ");

		
		Query query = getCurrentSession().createSQLQuery(sqlStmt.toString()); 
		query.setParameter("externalaccountkey", "501132150791");
		query.setParameter("state", "C");
		query.setParameter("statementdate1", tStartDate);
		query.setParameter("statementdate2", tEndDate);
		
		List<Long> result = query.list();

		} catch(Exception e) {
			System.out.println(e);
		}
	}
}